let url = require("url");
let ex = require("express")();
let fs = require("fs");
let exs = require("express-static");
let mysql = require("mysql");
const port = 502;
let sql = mysql.createConnection({
	host: 'localhost',
	user: 'root',
	password: '123456',
	database: 'students'
})
//鏈接
sql.connect();
//默认路由
ex.get("/", (req, res) => {
	fs.readFile("static/index.html", (err, data) => {
		if(err) {
			res.end(err);
			console.log(err);
		} else {
			res.end(data);
		}
	})
})
//查
ex.get("/sel", (req, res) => {
	sql.query(`select * from students order by id`, (err, data) => {
		if(err) {
			res.end("fail");
		} else {
			res.end(JSON.stringify(data))
		}
	})
})
//删
ex.get("/del", (req, res) => {
	let all = url.parse(req.url, true).query;
	let id = all.id;
	sql.query(`delete from students where id=${id}`, (err, data) => {
		if(err) {
			res.end("fail");
		} else {
			res.end("success");
		}
	})
})
//改
ex.get("/upt", (req, res) => {
	let all = url.parse(req.url, true).query;
	let id = all.id;
	let age = all.age;
	let sex = all.sex;
	let city = all.city;
	let name = all.name;
	sql.query(`update students set name="${name}",sex="${sex}",age="${age}",city="${city}" where id=${id}`, (err, data) => {
		if(err) {
			res.end("fail");
		} else {
			res.end("success");
		}
	})
})
//增
ex.get("/add", (req, res) => {
	let all = url.parse(req.url, true).query;
	let age = all.age;
	let sex = all.sex;
	let city = all.city;
	let name = all.name;
	sql.query(`insert into students (name,age,sex,city) values ("${name}","${age}","${sex}","${city}")`, (err, data) => {
		if(err) {
			res.end("fail");
		} else {
			res.end("success");
		}
	})
})
//注册
ex.get("/addun", (req, res) => {
	let all = url.parse(req.url, true).query;
	let uname = all.uname;
	let pwd = all.pwd;
	sql.query(`SELECT * FROM username where uname="${uname}"`, (err, data) => {
		if(err) {
			res.end("fail");
		} else {
			if(data[0]) {
				res.end("already");
			} else {
				sql.query(`insert into username (uname,pwd) values ("${uname}","${pwd}")`, (err, data) => {
					if(err) {
						res.end("fail");
					} else {
						res.end("success");
					}
				})
			}
		}
	})
})
//登录
ex.get("/selun", (req, res) => {
	let all = url.parse(req.url, true).query;
	let uname = all.uname;
	let pwd = all.pwd;
	sql.query(`SELECT * FROM username WHERE uname="${uname}"`, (err, data) => {
		if(err) {
			res.end("fail");
		} else {
			if(!data[0]) {
				res.end("nouser");
			} else {
				if(data[0].pwd === pwd) {
					res.end("success");
				} else {
					res.end("err");
				}
			}
		}
	})
})
//开放静态文件获取
ex.use(exs(`${__dirname}/static`));
//开始监听
ex.listen(port, () => {
	console.log(`server is running at ${port}`)
});
